//
//  userdb.m
//  tcpoctest
//
//  Created by apple on 13-3-11.
//  Copyright (c) 2013年 gump. All rights reserved.
//

#import "userdb.h"

@implementation userdb

-(void)creatTable
{
    if (db == nil) {
        return;
    }
    
    pthread_mutex_lock(&mutexdatabase);
    
    [db open];
    
    [db executeUpdate:@"create table if not exists users (mail text primary key,ps text,\
name text,province char(1),city char(1),sex char(1),needcheck char(1),\
age char(1),coin int,sign text,ppnumber int,glamour int,mymail text,strcoin text,\
distroble char(1),issound int)"];
    
    [db close];
    
    pthread_mutex_unlock(&mutexdatabase);
}

-(void)insertUser:(NSString *)mail withPS:(NSString *)ps
{
    if (db == nil) {
        return;
    }
    
    NSString *userps = [self searchPS:mail];
    
    pthread_mutex_lock(&mutexdatabase);
    
    NSString *strsql;
    if([userps isEqualToString:@""])
    {
        strsql = [NSString stringWithFormat:@"insert into users (mail,ps) values('%@','%@')"
                  ,mail,ps];
        
        [db open];
        [db executeUpdate:strsql];
        [db close];
    }
    else if(![userps isEqualToString:ps])
    {
        strsql = [NSString stringWithFormat:@"update users set ps = '%@' where mail = '%@'"
                  ,ps,mail];
        
        [db open];
        [db executeUpdate:strsql];
        [db close];
        
    }
    
   pthread_mutex_unlock(&mutexdatabase);
}

-(NSString *)searchPS:(NSString *)mail
{
    if (db == nil) {
        return @"";
    }
    
    NSString *strps = @"";
    NSString *strsql = [NSString stringWithFormat:@"SELECT ps FROM users WHERE mail = '%@'",mail];
    
    pthread_mutex_lock(&mutexdatabase);
    
    [db open];
    FMResultSet *s = [db executeQuery:strsql];
    while ([s next])
    {
        strps = [s stringForColumn:@"ps"];
    }
    [db close];
    
    pthread_mutex_unlock(&mutexdatabase);
    
    return strps;
}

-(void)insertUserByPP:(int)ppnum withPS:(NSString *)ps
{
    if (db == nil) {
        return;
    }
    
    NSString *userps = [self searchPSByPP:ppnum];
    
    pthread_mutex_lock(&mutexdatabase);
    
    NSString *strsql;
    if([userps isEqualToString:@""])
    {
        strsql = [NSString stringWithFormat:@"insert into users (ppnumber,ps) values(%d,'%@')"
                  ,ppnum,ps];
        
        [db open];
        [db executeUpdate:strsql];
        [db close];
    }
    else if(![userps isEqualToString:ps])
    {
        strsql = [NSString stringWithFormat:@"update users set ps = '%@' where ppnumber = %d"
                  ,ps,ppnum];
        
        [db open];
        [db executeUpdate:strsql];
        [db close];
        
    }
    
    pthread_mutex_unlock(&mutexdatabase);
}

-(NSString *)searchPSByPP:(int)ppnum
{
    if (db == nil) {
        return @"";
    }
    
    NSString *strps = @"";
    NSString *strsql = [NSString stringWithFormat:@"SELECT ps FROM users WHERE ppnumber = %d",ppnum];
    
    pthread_mutex_lock(&mutexdatabase);
    
    [db open];
    FMResultSet *s = [db executeQuery:strsql];
    while ([s next])
    {
        strps = [s stringForColumn:@"ps"];
    }
    [db close];
    
    pthread_mutex_unlock(&mutexdatabase);
    
    return strps;
}

-(void)initData
{
    if (db == nil) {
        return;
    }
    
    pthread_mutex_lock(&mutexdatabase);
    
    [db open];
    
    NSString *strsql = @"";
    FMResultSet *s = nil;
    
    if ([DataControl shareControl].personDS.uinfo.mail != nil)
    {
        strsql = [NSString stringWithFormat:@"SELECT \
name,province,city,sex,needcheck,age,coin,sign,ppnumber,glamour,mymail,strcoin,distroble,issound \
FROM users where mail = '%@'",[DataControl shareControl].personDS.uinfo.mail];
        
        s = [db executeQuery:strsql];
        if ([s next])
        {
            [DataControl shareControl].personDS.uinfo.name = [s stringForColumn:@"name"];
            [DataControl shareControl].personDS.uinfo.province = [s intForColumn:@"province"];
            [DataControl shareControl].personDS.uinfo.city = [s intForColumn:@"city"];
            [DataControl shareControl].personDS.uinfo.sex = [s intForColumn:@"sex"];
            [DataControl shareControl].personDS.uinfo.needcheck = [s intForColumn:@"needcheck"];
            [DataControl shareControl].personDS.uinfo.age = [s intForColumn:@"age"];
            [DataControl shareControl].personDS.uinfo.iCoin = [s intForColumn:@"coin"];
            [DataControl shareControl].personDS.uinfo.strSign = [s stringForColumn:@"sign"];
            if ([[DataControl shareControl].personDS.uinfo.strSign isEqualToString:@"(null)"]) {
                [DataControl shareControl].personDS.uinfo.strSign = @"";
            }
            [DataControl shareControl].personDS.uinfo.ppNumber = [s intForColumn:@"ppnumber"];
            [DataControl shareControl].personDS.uinfo.iGlamour = [s intForColumn:@"glamour"];
            [DataControl shareControl].personDS.uinfo.mymail = [s stringForColumn:@"mymail"];
            [DataControl shareControl].personDS.uinfo.strcoin = [s stringForColumn:@"strcoin"];
            [DataControl shareControl].personDS.uinfo.cDisturb = [s intForColumn:@"distroble"];
            [DataControl shareControl].personDS.uinfo.bSound = [s intForColumn:@"issound"];
        }
        
    }
    
    [db close];
    
    pthread_mutex_unlock(&mutexdatabase);

}

-(void)updateUserInfo
{
    if (db == nil) {
        return;
    }
    
    NSString *strsql = @"";
    switch ([DataControl shareControl].iUUT)
    {
        case UUT_AGE:
        {
            strsql = [NSString stringWithFormat:@"update users set age = '%d' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.age,
                      [DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_SEX:
        {
            strsql = [NSString stringWithFormat:@"update users set sex = '%d' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.sex,
                      [DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_PROVINCE:
        {
            strsql = [NSString stringWithFormat:@"update users set province = '%d',city = '%d' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.province
                      ,[DataControl shareControl].personDS.uinfo.city
                      ,[DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_SIGN:
        {
            strsql = [NSString stringWithFormat:@"update users set sign = '%@' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.strSign,
                      [DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_CHECK:
        {
            strsql = [NSString stringWithFormat:@"update users set needcheck = '%d' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.needcheck,
                      [DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_NAME:
        {
            strsql = [NSString stringWithFormat:@"update users set name = '%@' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.name,
                      [DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_ALL:
        {
            strsql = [NSString stringWithFormat:@"update users set name = '%@'\
,needcheck = '%d',sign = '%@',province = '%d',city = '%d',\
sex = '%d',age = '%d',distroble = '%d',issound = %d where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.name
                      ,[DataControl shareControl].personDS.uinfo.needcheck
                      ,[DataControl shareControl].personDS.uinfo.strSign
                      ,[DataControl shareControl].personDS.uinfo.province
                      ,[DataControl shareControl].personDS.uinfo.city
                      ,[DataControl shareControl].personDS.uinfo.sex
                      ,[DataControl shareControl].personDS.uinfo.age
                      ,[DataControl shareControl].personDS.uinfo.cDisturb
                      ,[DataControl shareControl].personDS.uinfo.bSound
                      ,[DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_PS:
        {
            strsql = [NSString stringWithFormat:@"update users set ps = '%@' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.ps
                      ,[DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_SELFMAIL:
        {
            strsql = [NSString stringWithFormat:@"update users set mymail = '%@' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.mymail
                      ,[DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        case UUT_COIN:
        {
            strsql = [NSString stringWithFormat:@"update users set coin = %d where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.iCoin
                      ,[DataControl shareControl].personDS.uinfo.mail];

        }
            break;
        case UUT_STRCOIN:
        {
            strsql = [NSString stringWithFormat:@"update users set strcoin = '%@' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.strcoin
                      ,[DataControl shareControl].personDS.uinfo.mail];
        }
            break;
            case UUT_DISTRO:
        {
            strsql = [NSString stringWithFormat:@"update users set distroble = '%d' where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.cDisturb
                      ,[DataControl shareControl].personDS.uinfo.mail];
        }
            break;
            case UUT_SOUND:
        {
            strsql = [NSString stringWithFormat:@"update users set issound = %d where mail = '%@'"
                      ,[DataControl shareControl].personDS.uinfo.bSound
                      ,[DataControl shareControl].personDS.uinfo.mail];
        }
            break;
        default:
            break;
    }
    
    pthread_mutex_lock(&mutexdatabase);
    
    [db open];
    [db executeUpdate:strsql];
    [db close];
    
    pthread_mutex_unlock(&mutexdatabase);
}

@end
